package gestion.camping.dao.impl;

import gestion.camping.dao.GenericDAO;
import gestion.camping.dto.Personne;
import gestion.camping.dto.Reservation;
import gestion.camping.dto.StatutReservation;
import gestion.camping.dto.Visiteur;
import gestion.camping.utils.DateFormatUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;

/**
 * Permet la lecture, la modification, l'ajout ou la suppresion en base des enregistrement de la table Personne
 * 
 * @author Gombert Damien
 * @author Foucras Melanie
 * @author Germa Romain
 * 
 * @version 1.0
 */
public class ReservationDao extends GenericDAO<Reservation>{
	
	private PersonneDao personneDao;
	private TypeLocationDao typeLocationDao;
	private BadgeDao badgeDao;
	private CarteDao carteDao;
	private StatutReservationDao statutDao;
	private EmplacementDao emplacementDao;

	public ReservationDao(Connection conn) {
		super(conn);
		personneDao = new PersonneDao(conn);
		typeLocationDao = new TypeLocationDao(conn);
		badgeDao = new BadgeDao(conn);
		carteDao = new CarteDao(conn);
		statutDao = new StatutReservationDao(conn);
		emplacementDao = new EmplacementDao(conn);
	}

	@Override
	public Reservation create(Reservation obj) {
		// TODO Auto-generated method stub
		return null;
	}
	
	public Reservation create(Reservation obj, double arrhe, double fraisResa) {
		
		try {
			
			connect.setAutoCommit(false);
			
			
			if(obj.getClient().getId() == 0){
				personneDao.create(obj.getClient());
			}
			
			
			OracleCallableStatement oraCallStmt = (OracleCallableStatement) connect.prepareCall(
			        "{call RESERVATION_AJOUT(?, ?, ?, ?, ?, ?, ?)}"
			    ); 
			
			
			oraCallStmt.registerOutParameter(7, OracleTypes.NUMBER);
			oraCallStmt.setLong(1, obj.getClient().getId());
			oraCallStmt.setString(2, DateFormatUtils.formatDate(obj.getDateDebut()));
			oraCallStmt.setString(3, DateFormatUtils.formatDate(obj.getDateFin()));
			oraCallStmt.setInt(4, Integer.parseInt(String.valueOf(obj.getTypeLocation().getId())));
			oraCallStmt.setDouble(5, arrhe);
			oraCallStmt.setDouble(6, fraisResa);
			oraCallStmt.execute();

			System.out.println(oraCallStmt.getInt(7));
          
			oraCallStmt.close();
          
			obj = this.find(oraCallStmt.getInt(7));
									
		} catch (SQLException e) {
			try {
				connect.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
			gestionErreurSQL(e);
		}
		
		
		try {
			connect.commit();
			connect.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return obj;
	}

	@Override
	public boolean delete(Reservation obj) {
		return false;
	}
	
	public Reservation confirmation(Reservation obj, double arrhe, double fraisResa) {

		try {
							
			
			OracleCallableStatement oraCallStmt = (OracleCallableStatement) connect.prepareCall(
			        "{call RESERVATION_CONFIRMATION(?, ?, ?)}"
			    ); 
			
			oraCallStmt.setLong(1, obj.getId());
			oraCallStmt.setDouble(2, arrhe);
			oraCallStmt.setDouble(3, fraisResa);
			oraCallStmt.execute();

          
			oraCallStmt.close();
          
			obj = this.find(Integer.valueOf(String.valueOf(obj.getId())));
									
		} catch (SQLException e) {			
			gestionErreurSQL(e);
		}
		
		return obj;
	}

	@Override
	public Reservation update(Reservation obj) {
				
		try {
			Statement smt = connect.createStatement();
			
			//On récupère le id qu'aura notre futur enregistrement
			//TODO Controler la date version
			
			//On prépare la requête de modification
			String rqt = "";
			
			//On trace la requête dans la console
			System.out.println(rqt);
			
			//ON effectue l'insertion en base de données		
			smt.executeUpdate(rqt);
			
			//On ferme la transaction
			smt.close();
									
		} catch (SQLException e) {
			gestionErreurSQL(e);
		}
		
		return obj;
	}

	@Override
	public Reservation find(int id) {
		Reservation dto = new Reservation();
		
		try {
			
			Statement smt = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			
			String rqt = "SELECT * FROM RESERVATION R WHERE R.RESERVATION_NUM="+ id+" ORDER BY RESERVATION_NUM";
			
			//Execution de la requ�te
			ResultSet res = smt.executeQuery(rqt);		
			
			// si il y a des r�sultat, on se positionne sur le premier enregistrement
			if(res.first()){
				// Et on initialise le dto
				dto.setId(res.getInt("RESERVATION_NUM"));
				dto.setClient(personneDao.find(res.getInt("CLIENT_NUM")));
				dto.setARRHE(res.getBoolean("RESERVATION_ARRHE"));
				dto.setFraisResa(res.getBoolean("RESERVATION_FRAIS_RESERVATION"));
				dto.setMontant(res.getDouble("RESERVATION_MONTANT"));
				dto.setDateDebut(res.getDate("RESERVATION_DATE_DEBUT"));
				dto.setDateFin(res.getDate("RESERVATION_DATE_FIN"));
				dto.setBadge(badgeDao.find(res.getInt("BADGE_NUM")));
				dto.setCarte(carteDao.find(res.getInt("CARTE_NUM")));
				dto.setStatut(statutDao.find(res.getInt("STATUT_NUM")));
				dto.setEmplacement(emplacementDao.find(res.getInt("EMPLACEMENT_NUM")));
				dto.setTypeLocation(typeLocationDao.find(res.getInt("TYPELOCATION_NUM")));
			}
			
			//On fini la transaction
			smt.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return dto;
	}
	
	/**
	 * Récupère l'ensemble des reservations
	 * 
	 * @return toutes les reservations présentes dans la base 
	 */
	public List<Reservation> findAllReservation(){
		List<Reservation> listReservation = new ArrayList<Reservation>();
		
		try {
			
			Statement smt = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			
			String rqt = "SELECT * FROM RESERVATION R ORDER BY RESERVATION_NUM";
			
			//Execution de la requ�te
			ResultSet res = smt.executeQuery(rqt);		
			
			//On crée un dtoPersonne temporaire
			Reservation dto;
			
			// Tant qu'il y a des résultat
			while(res.next()){
				//on initialise le dto
				dto = new Reservation();
				dto.setId(res.getLong("RESERVATION_NUM"));
				dto.setClient(personneDao.find(res.getInt("CLIENT_NUM")));
				dto.setARRHE(res.getBoolean("RESERVATION_ARRHE"));
				dto.setFraisResa(res.getBoolean("RESERVATION_FRAIS_RESERVATION"));
				dto.setMontant(res.getDouble("RESERVATION_MONTANT"));
				dto.setDateDebut(res.getDate("RESERVATION_DATE_DEBUT"));
				dto.setDateFin(res.getDate("RESERVATION_DATE_FIN"));
				dto.setBadge(badgeDao.find(res.getInt("BADGE_NUM")));
				dto.setCarte(carteDao.find(res.getInt("CARTE_NUM")));
				dto.setStatut(statutDao.find(res.getInt("STATUT_NUM")));
				dto.setEmplacement(emplacementDao.find(res.getInt("EMPLACEMENT_NUM")));
				dto.setTypeLocation(typeLocationDao.find(res.getInt("TYPELOCATION_NUM")));
				
				//Et on ajoute le dto à la liste
				listReservation.add(dto);
			}
						
			//On ferme la transaction
			smt.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return listReservation;
	}


	public Double calculTotal(Reservation reservation) {
		OracleCallableStatement oraCallStmt;
		Double result = null;
		try {
			oraCallStmt = (OracleCallableStatement) connect.prepareCall(
			        "{? = call CALCUL_TARIF(?, ?, ?)}"
			    ); 
			
			oraCallStmt.registerOutParameter(1, OracleTypes.NUMBER);
			oraCallStmt.setString(2, DateFormatUtils.formatDate(reservation.getDateDebut()));
			oraCallStmt.setString(3, DateFormatUtils.formatDate(reservation.getDateFin()));
			oraCallStmt.setInt(4, Integer.parseInt(String.valueOf(reservation.getTypeLocation().getId())));
			oraCallStmt.execute();

            result = oraCallStmt.getDouble(1);

            
	         oraCallStmt.close();
	         
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		
		return result ;
	}


	public boolean testDisponibilite(Reservation reservation) {
		OracleCallableStatement oraCallStmt;
		int result = -1;
		try {
			oraCallStmt = (OracleCallableStatement) connect.prepareCall(
			        "{? = call TEST_RESERVATION(?, ?, ?)}"
			    ); 
			
			oraCallStmt.registerOutParameter(1, OracleTypes.NUMBER);
			oraCallStmt.setString(2, DateFormatUtils.formatDate(reservation.getDateDebut()));
			oraCallStmt.setString(3, DateFormatUtils.formatDate(reservation.getDateFin()));
			oraCallStmt.setInt(4, Integer.parseInt(String.valueOf(reservation.getTypeLocation().getId())));
			oraCallStmt.execute();

            result = oraCallStmt.getInt(1);

            
	         oraCallStmt.close();
	         
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		
		return result > 0 ? true : false;
	}

	public List<Personne> ajoutOccupant(List<Personne> listOccupant, Reservation reservation) {
		
		try {
			
			connect.setAutoCommit(false);
			
			for(Personne occupant : listOccupant){
			
				if(occupant.getId() == 0){
					personneDao.create(occupant);
				}
				
				OracleCallableStatement oraCallStmt = (OracleCallableStatement) connect.prepareCall(
			        "{call OCCUPANT_AJOUT(?, ?)}"
			    ); 	
		
				oraCallStmt.setLong(1, occupant.getId());
				oraCallStmt.setLong(2, reservation.getId());
				
				oraCallStmt.execute();
	

	          
				oraCallStmt.close();			
			}
			
		} catch (SQLException e) {
			try {
				connect.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
			gestionErreurSQL(e);
		}
		
		try {
			connect.commit();
			connect.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return this.findOccupant(reservation);		
	}

	public List<Personne> findOccupant(Reservation reservation) {
		List<Personne> listPersonne = new ArrayList<Personne>();
		
		try {
			
			Statement smt = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			
			String rqt = "SELECT * FROM OCCUPANT O WHERE O.RESERVATION_NUM="+reservation.getId()+" ORDER BY RESERVATION_NUM";
			
			//Execution de la requ�te
			ResultSet res = smt.executeQuery(rqt);		
			
			//On crée un dtoPersonne temporaire
			Personne dto;
			
			// Tant qu'il y a des résultat
			while(res.next()){
				//on initialise le dto
				dto = new Personne();
				dto = personneDao.find(res.getInt("PERSONNE_NUM"));
				
				//Et on ajoute le dto à la liste
				listPersonne.add(dto);
			}
						
			//On ferme la transaction
			smt.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return listPersonne;
	}

	public List<Visiteur> ajoutVisiteur(List<Visiteur> listVisiteur, Reservation reservation) {
		
		try {
			
			connect.setAutoCommit(false);
			
			for(Visiteur visiteur : listVisiteur){
			
				if(visiteur.getPersonne().getId() == 0){
					personneDao.create(visiteur.getPersonne());
				}
				
				OracleCallableStatement oraCallStmt = (OracleCallableStatement) connect.prepareCall(
			        "{call VISITEUR_AJOUT(?, ?)}"
			    ); 	
		
				oraCallStmt.setLong(1, visiteur.getPersonne().getId());
				oraCallStmt.setLong(2, reservation.getId());
					
				oraCallStmt.execute();
	

	          
				oraCallStmt.close();			
			}
			
		} catch (SQLException e) {
			try {
				connect.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
			gestionErreurSQL(e);
		}
		
		try {
			connect.commit();
			connect.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return this.findVisiteur(reservation);
	}

	public List<Visiteur> findVisiteur(Reservation reservation) {
		List<Visiteur> listPersonne = new ArrayList<Visiteur>();
		
		try {
			
			Statement smt = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			
			String rqt = "SELECT * FROM VISITEUR V WHERE V.RESERVATION_NUM="+reservation.getId()+" ORDER BY RESERVATION_NUM";
			
			//Execution de la requ�te
			ResultSet res = smt.executeQuery(rqt);		
			
			//On crée un dtoPersonne temporaire
			Visiteur dto;
			
			// Tant qu'il y a des résultat
			while(res.next()){
				//on initialise le dto
				dto = new Visiteur();
				dto.setPersonne(personneDao.find(res.getInt("PERSONNE_NUM")));
				dto.setDateVisite(res.getDate("VISITEUR_DATE"));
				
				//Et on ajoute le dto à la liste
				listPersonne.add(dto);
			}
						
			//On ferme la transaction
			smt.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return listPersonne;
	}

	public Reservation attributionCarte(Reservation reservation) {
		try {
				
				
			OracleCallableStatement oraCallStmt = (OracleCallableStatement) connect.prepareCall(
		        "{call RESERVATION_ARRIVEE(?, ?)}"
		    ); 	
	
			oraCallStmt.setLong(1, reservation.getId());
			oraCallStmt.setString(2, "10000");
				
			oraCallStmt.execute();


          
			oraCallStmt.close();			
		
			
		} catch (SQLException e) {			
			gestionErreurSQL(e);
		}
		
		
		return this.find(Integer.valueOf(String.valueOf(reservation.getId())));
		
	}

	public List<Reservation> findByStatutDateId(StatutReservation statut, Date dateDeb, Date dateFin, Integer id) {
		List<Reservation> listReservation = new ArrayList<Reservation>();
		
		try {
			
			Statement smt = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			
			StringBuilder rqt = new StringBuilder("SELECT * FROM RESERVATION R ");
			
			boolean clause = false;
			if(statut != null){
				rqt.append(" WHERE R.STATUT_NUM="+statut.getId());
				clause = true;
			}
			
			if(dateDeb != null){
				if(clause){
					rqt.append(" AND ");
				}else{
					rqt.append(" WHERE ");
				}
				
				rqt.append(" R.RESERVATION_DATE_DEBUT='"+DateFormatUtils.formatDate(dateDeb)+"'");
			}
			
			if(dateFin != null){
				if(clause){
					rqt.append(" AND ");
				}else{
					rqt.append(" WHERE ");
				}
				
				rqt.append(" R.RESERVATION_DATE_FIN='"+DateFormatUtils.formatDate(dateFin)+"'");
			}
			
			if(id != null){
				if(clause){
					rqt.append(" AND ");
				}else{
					rqt.append(" WHERE ");
				}
				
				rqt.append(" R.RESERVATION_NUM="+id.intValue()+"");
			}
			
			System.out.println(rqt.toString());
			//Execution de la requ�te
			ResultSet res = smt.executeQuery(rqt.toString());		
			
			//On crée un dtoPersonne temporaire
			Reservation dto;
			
			// Tant qu'il y a des résultat
			while(res.next()){
				//on initialise le dto
				dto = new Reservation();
				dto.setId(res.getLong("RESERVATION_NUM"));
				dto.setClient(personneDao.find(res.getInt("CLIENT_NUM")));
				dto.setARRHE(res.getBoolean("RESERVATION_ARRHE"));
				dto.setFraisResa(res.getBoolean("RESERVATION_FRAIS_RESERVATION"));
				dto.setMontant(res.getDouble("RESERVATION_MONTANT"));
				dto.setDateDebut(res.getDate("RESERVATION_DATE_DEBUT"));
				dto.setDateFin(res.getDate("RESERVATION_DATE_FIN"));
				dto.setBadge(badgeDao.find(res.getInt("BADGE_NUM")));
				dto.setCarte(carteDao.find(res.getInt("CARTE_NUM")));
				dto.setStatut(statutDao.find(res.getInt("STATUT_NUM")));
				dto.setEmplacement(emplacementDao.find(res.getInt("EMPLACEMENT_NUM")));
				dto.setTypeLocation(typeLocationDao.find(res.getInt("TYPELOCATION_NUM")));
				
				//Et on ajoute le dto à la liste
				listReservation.add(dto);
			}
						
			//On ferme la transaction
			smt.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return listReservation;
	}

	public Reservation restitutionCarte(Reservation reservation) {
		try {
			
			OracleCallableStatement oraCallStmt = (OracleCallableStatement) connect.prepareCall(
		        "{call RESERVATION_DEPART(?)}"
		    ); 	
	
			oraCallStmt.setLong(1, reservation.getId());
				
			oraCallStmt.execute();
          
			oraCallStmt.close();			
		
			
		} catch (SQLException e) {			
			gestionErreurSQL(e);
		}
		
		return this.find(Integer.valueOf(String.valueOf(reservation.getId())));

	}	
}
